package edu.friday.repository.custom.impl;

import edu.friday.repository.custom.GenTableCustomRepository;
import edu.friday.model.GenTable;
import edu.friday.model.dto.GenTableDTO;
import edu.friday.utils.StringUtils;
import org.hibernate.query.NativeQuery;
import org.springframework.data.domain.Pageable;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.ArrayList;
import java.util.List;

public class GenTableCustomRepositoryImpl implements GenTableCustomRepository {

    @PersistenceContext
    private EntityManager entityManager;


    @Override
    public List<GenTableDTO> selectDbTableList(GenTable genTable, Pageable page) {
        String tableName = genTable.getTableName();
        String tableComment = genTable.getTableComment();
        boolean queryTableName = null != genTable && !StringUtils.isBlank(tableName);
        boolean queryTableComment = null != genTable && !StringUtils.isBlank(tableComment);
        StringBuffer sql = new StringBuffer();
        sql.append(" select table_name, table_comment, create_time, update_time from information_schema.tables ");
        sql.append(" where table_schema = (select database()) ");
        sql.append(" AND table_name NOT LIKE 'qrtz_%' AND table_name NOT LIKE 'gen_%' ");
        sql.append(" AND table_name NOT IN (select table_name from gen_table) ");
        if(queryTableName) {
            sql.append(" AND lower(table_name) like lower(concat('%', :tableName, '%')) ");
        }
        if(queryTableComment) {
            sql.append(" AND lower(table_comment) like lower(concat('%', :tableComment, '%')) ");
        }
        Query query = entityManager.createNativeQuery(sql.toString(), GenTableDTO.class)
                .setFirstResult((int) page.getOffset()) // offset = currentPage * pageSize
                .setMaxResults(page.getPageSize());
        if(queryTableName) {
            query.setParameter("tableName", tableName);
        }
        if(queryTableComment) {
            query.setParameter("tableComment", tableComment);
        }
        return query.getResultList();
    }

    @Override
    public long countDbTableList(GenTable genTable) {
        String tableName = genTable.getTableName();
        String tableComment = genTable.getTableComment();
        boolean queryTableName = null != genTable && !StringUtils.isBlank(tableName);
        boolean queryTableComment = null != genTable && !StringUtils.isBlank(tableComment);
        StringBuffer sql = new StringBuffer();
        sql.append(" select count(*) from information_schema.tables ");
        sql.append(" where table_schema = (select database()) ");
        sql.append(" AND table_name NOT LIKE 'qrtz_%' AND table_name NOT LIKE 'gen_%' ");
        sql.append(" AND table_name NOT IN (select table_name from gen_table) ");
        if(queryTableName) {
            sql.append(" AND lower(table_name) like lower(concat('%', :tableName, '%')) ");
        }
        if(queryTableComment) {
            sql.append(" AND lower(table_comment) like lower(concat('%', :tableComment, '%')) ");
        }
        Query query = entityManager.createNativeQuery(sql.toString());
        if(queryTableName) {
            query.setParameter("tableName", tableName);
        }
        if(queryTableComment) {
            query.setParameter("tableComment", tableComment);
        }
        return Long.parseLong(query.getSingleResult().toString());
    }

    @Override
    public List<GenTableDTO> selectDbTableListByNames(String[] tableNames) {
        if (null == tableNames || tableNames.length == 0){
            return new ArrayList<GenTableDTO>();
        }
        StringBuffer sql = new StringBuffer();
        sql.append(" select table_name, table_comment, create_time, update_time from information_schema.tables ");
        sql.append(" where table_name NOT LIKE 'qrtz_%' and table_name NOT LIKE 'gen_%' and table_schema = (select database()) ");
        sql.append(" and table_name in (:tableNames) ");
        Query query = entityManager.createNativeQuery(sql.toString(), GenTableDTO.class)
                .unwrap(NativeQuery.class)
                .setParameterList("tableNames", tableNames);
        return query.getResultList();
    }
}

